# -*- coding:utf-8 -*-
# __author__ = "melon"

import psycopg2
from utils.tools import getAppConf


def get_db_conf():
    """
    获取database配置信息
    :return:
    """
    app_conf = getAppConf()['postgreSQL']
    db_conf_result = dict()
    db_conf_result['db_user'] = app_conf['user']
    db_conf_result['db_passwd'] = app_conf['pwd']
    db_conf_result['db_host'] = app_conf['host']
    db_conf_result['db_name'] = app_conf['dbname']
    db_conf_result['db_port'] = app_conf['port']
    return db_conf_result


class DbConn(object):
    """
    打开一个database连接
    """
    def __init__(self):
        self.db_conf = get_db_conf()
        self.db_name = self.db_conf["db_name"]

    def fetch_one(self, sql, param=None):
        with psycopg2.connect(database=self.db_name, user=self.db_conf.get("db_user"),
                              password=self.db_conf.get("db_passwd"), host=self.db_conf.get("db_host"),
                              port=self.db_conf.get("db_port")) as conn:
            with conn.cursor() as cur:
                try:
                    cur.execute(sql, param)
                    rows = cur.fetchone()
                    column_names = [desc[0] for desc in cur.description]

                    result = gen_result(column_names, rows)[0] if rows else None
                except Exception as e:
                    raise e
                return result

    def fetch_all(self, sql, param=None):
        with psycopg2.connect(database=self.db_name, user=self.db_conf.get("db_user"),
                              password=self.db_conf.get("db_passwd"), host=self.db_conf.get("db_host"),
                              port=self.db_conf.get("db_port")) as conn:
            with conn.cursor() as cur:
                try:
                    cur.execute(sql, param)
                    rows = cur.fetchall()
                    column_names = [desc[0] for desc in cur.description]
                    result = gen_result(column_names, rows)
                except Exception as e:
                    raise e
                return result

    
    def commit(self, sql, param=None):
        is_success = False
        with psycopg2.connect(database=self.db_name, user=self.db_conf.get("db_user"),
                              password=self.db_conf.get("db_passwd"), host=self.db_conf.get("db_host"),
                              port=self.db_conf.get("db_port")) as conn:
            with conn.cursor() as cur:
                try:
                    n = cur.execute(sql, param)
                    n = conn.commit()
                    is_success = True
                except Exception as e:
                    conn.rollback()
                    is_success = False
                    raise e
                    
        return is_success


def gen_result(col_names, rows):
    """
    封装数据库中得到的数据和列名
    :param col_names:
    :param rows:
    :return:
    """
    result = []
    if isinstance(rows, list):
        for row in rows:
            tmp = zip(col_names, row)
            rst_tmp = {}
            for each in tmp:
                tmp_d = {each[0]: each[1]}
                rst_tmp.update(tmp_d)
            result.append(rst_tmp)
    else:
        tmp = zip(col_names, rows)
        rst_tmp = {}
        for each in tmp:
            tmp_d = {each[0]: each[1]}
            rst_tmp.update(tmp_d)
        result.append(rst_tmp)
    return result

db = DbConn()

###
class SQLQueryPagination(object):
    def __init__(self, num_per_page):
        """
        :param db_name: postgres connection
        :param num_per_page: num each page
        """
        self.num_per_page = num_per_page
        self.db_conf = get_db_conf()
        self.db_name = self.db_conf.get("db_name")
        self.conn = psycopg2.connect(database=self.db_name, user=self.db_conf.get("db_user"),
                                     password=self.db_conf.get("db_passwd"), host=self.db_conf.get("db_host"),
                                     port=self.db_conf.get("db_port"))

    def cal_total_row_num(self, sql, param=None):
        sql = "SELECT COUNT(*) FROM (%s) as this_table" % sql
        cur = self.conn.cursor()
        if not sql:
            cur.close()
            raise KeyError
        else:
            if param:
                cur.execute(sql, param)
            else:
                cur.execute(sql)
            row_num = cur.fetchone()
            cur.close()
            total_row_num = int(row_num[0] if row_num else 0)
            return total_row_num

    def cal_total_pages(self, sql, param=None):
        total_row_num = self.cal_total_row_num(sql, param)
        if total_row_num % self.num_per_page == 0:
            total_page = total_row_num / self.num_per_page

        else:
            total_page = total_row_num / self.num_per_page + 1
        return total_page

    def cal_start_index(self, curr_page_index):
        start_index = (curr_page_index - 1) * self.num_per_page
        return start_index

    def cal_end_index(self, total_row, total_page, curr_page_index):
        """
        :param total_row: 总行数
        :param total_page: 总页数
        :param curr_page_index: 现在所在的页数
        :return:
        """
        end_index = 0
        # 如果总行数小于每页需要的行数 那么返回当页下标
        if total_row < self.num_per_page:
            end_index = curr_page_index
        # 如果总行数正好可以满足每页 或者有剩余 并且现在不是最后一行
        elif total_row % self.num_per_page == 0 or total_row % self.num_per_page != 0 and curr_page_index < total_page:
            end_index = curr_page_index * self.num_per_page
        # 如果现在不是最后一行
        elif total_row % self.num_per_page != 0 and curr_page_index == total_page:
            end_index = total_row

        return end_index

    def query_each_page(self, sql, current_page_index, param=None):
        cur = self.conn.cursor()
        start_index = self.cal_start_index(current_page_index)
        q_sql = "SELECT * FROM (%s) as this_table limit %s offset %s" % (sql, self.num_per_page, start_index)
        if param:
            cur.execute(q_sql, param)
            rows = cur.fetchall()
        else:
            cur.execute(q_sql)
            rows = cur.fetchall()
        column_names = [desc[0] for desc in cur.description]
        data_each_page = gen_result(column_names, rows)
        cur.close()
        return data_each_page

    def query_result(self, sql, param=None):
        total_page_num = self.cal_total_pages(sql, param)
        # print total_page_num
        for page in range(total_page_num):
            yield self.query_each_page(sql, page, param)

